'''
Author xzk
Create Time 2020.7.17上午
Change Time1 2020.7.17上午
Pull Time 2020.7.17晚上
Results 对数据库的各类查询操作
'''

import pymysql


# 查询指定车位是否空闲（车位ID） 返回值：True（空闲） False（占用）
def query_one_parking_space(id):
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select IsFree from slots where ID=%s"
    cls.execute(sql, [id])
    isfree = cls.fetchone()
    result = ''
    if isfree[0] == 0:
        result = True
    elif isfree[0] == 1:
        result = False
    conn.close()
    return result


# 查询所有车位状况（） 返回值：（一个0-24的字典数组，如果对应车位有车数组元素中放车牌号，没有的话为None）
def query_all_parking_space():
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select * from slots"
    cls.execute(sql)
    result = cls.fetchall()

    parking_list = []
    for i in range(len(result)):
        parking = {'车位': result[i][0], '车牌': result[i][1], '车型': result[i][2]}
        parking_list.append(parking)

    conn.close()
    return parking_list


# 按车牌查询（车牌号） 返回值：（车牌号对应的停车位，如果没有这个车牌返回None）
def query_car_plate(car_plate):
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select ID from slots where Car_Plate=%s"
    cls.execute(sql, [car_plate])
    parking_id = cls.fetchone()
    if parking_id is None:
        result = None
    else:
        result = parking_id[0]

    conn.close()
    return result


# 按车牌查询未出库记录（车牌号）  返回值：该车牌号下没有离开时间的记录（一个元组）
def query_carinpark_by_plate(car_plate):
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select * from parking_record where car_plate=%s and leave_time is null"
    cls.execute(sql, [car_plate])
    result = cls.fetchone()
    conn.close()
    return result


# 查询未出库记录数量
def query_carinpark_count():
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select count(*) from parking_record where leave_time is null"
    cls.execute(sql)
    result = cls.fetchone()
    conn.close()
    return result[0]



def query_scanall_parking_space():
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select * from slots"
    cls.execute(sql)
    result = cls.fetchall()

    parking_list = []
    for i in range(len(result)):
        parking = {'id': result[i][0], 'chepai': result[i][1], 'chexing': result[i][2]}
        parking_list.append(parking)

    conn.close()
    return parking_list

if __name__ == '__main__':
    #print(query_one_parking_space(0))
    #print(query_one_parking_space(1))
    print(query_scanall_parking_space())
    #print(query_car_plate('皖A88888'))
    #print(query_car_plate('皖A84568'))
    #print(query_carinpark_by_plate('皖A88888'))
